package com.xiaoliang.tools.JDBCUtils;



import org.apache.commons.beanutils.BeanUtils;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author XIAOLIANG
 * @ClassName JDBCUtils.java
 * @Description TODO
 * @createTime 2021年01月26日 14:52:00
 */
public class JDBCUtils {

	private static  String url ;
	private static  String user ;
	private static  String password ;
	private static  String driver ;

	private static  ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

	static {

		try {
			InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

			Properties properties = new Properties();
			properties.load(is);
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}


	public static Connection getConnection(){
		Connection conn;

		try {
			if (threadLocal.get()==null){
			conn = DriverManager.getConnection(url, user, password);
			threadLocal.set(conn);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}

		return threadLocal.get();
	}

	public static void close(Connection conn, PreparedStatement ps){
		if (ps!=null){
			try {
				ps.close();
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
		if (conn!=null){
			try {
				conn.close();
				threadLocal.remove();
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}

	public static void close(Connection conn, PreparedStatement ps, ResultSet resultSet){
		if (resultSet!=null){
			try {
				resultSet.close();
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
		close(conn,ps);
	}


	/**
	 * 一个通用的 执行增删改SQL的方法
	 * @param sql  传入需要执行的SQL
	 *
	 * @param args  传入占位符 参数
	 * @return  返回受影响的行数
	 */
	public static int  update(String sql,Object ...args){

		int re = 0;
		Connection conn;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
//			System.out.println(conn);
			ps = conn.prepareStatement(sql);

			for (int i = 1; i <= args.length; i++) {

				ps.setObject(i,args[i-1]);

			}

			 re = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException(e);
		} finally {
			close(null,ps,null);
		}
		return re;

	}


	/**
	 * 通用的查询方法
	 * @param sql 传入需要执行的sql 可以使用占位符
	 * @param tClass 传入 实体类的类对象
	 * @param args sql占位符 参数
	 * @return 返回传入的实体类的对象集合
	 *
	 * 注意!
	 * 注意!
	 * 注意!
	 *                 使用此方法 查询语句 查询的结果列 必须加别名 且别名必须和实体类属性一致 !!!!
	 */
	public static <T> List<T> query(String sql, Class<T> tClass, Object ...args){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;//传入sql
		ArrayList<T> list = new ArrayList<>();
		try {


			conn = getConnection();
			ps = conn.prepareStatement(sql);


			for (int i = 0; i < args.length; i++) {      // 处理传入参数
				ps.setObject(i+1,args[i]);
			}
			resultSet = ps.executeQuery();   //提交sql

			//T t = tClass.newInstance();
			Field[] declaredFields = tClass.getDeclaredFields();  //通过反射拿到所有的属性对象

			ResultSetMetaData metaData = resultSet.getMetaData(); //获取元数据

			int columnCount = metaData.getColumnCount(); //获取列数

			while (resultSet.next()){

				T t = tClass.newInstance();

				for (int i = 1; i <= columnCount; i++) {
					String catalogName = metaData.getColumnLabel(i); //获取列名
//					System.out.println(catalogName);
					String value = String.valueOf( resultSet.getObject(i)); //列值 转成字符串的形式
					BeanUtils.copyProperty(t,catalogName,value);  //属性拷贝
				}
				list.add(t);
			}
			return list;
		} catch (SQLException | InvocationTargetException | IllegalAccessException | InstantiationException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JDBCUtils.close(null,ps,resultSet);
		}


	}

	/**
	 * 通用的查询方法 只查询一条记录
	 * @param sql  需要执行的sql语句
	 * @param tClass  需要返回的实体类的类对象
	 * @param args  占位符 参数

	 * @return  返回实体类的对象
	 *
	 * 	注意!
	 * 	注意!
	 * 	注意!
	 * 	             使用此方法 查询语句 查询的结果列 必须加别名 且别名必须和实体类属性一致 !!!!
	 */
	public static <T> T queryOne(String sql,Class<T> tClass,Object ...args){

		Connection conn;
		PreparedStatement ps = null;
		ResultSet resultSet = null;//传入sql

		try {

			conn = getConnection();
//			System.out.println(conn+"queryOne");
			ps = conn.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {      // 处理传入参数
				ps.setObject(i+1,args[i]);
			}
			resultSet = ps.executeQuery();   //提交sql

			//T t = tClass.newInstance();
			Field[] declaredFields = tClass.getDeclaredFields();  //通过反射拿到所有的属性对象

			ResultSetMetaData metaData = resultSet.getMetaData(); //获取元数据

			int columnCount = metaData.getColumnCount(); //获取列数

				T t = null;

			while (resultSet.next()){
					t = tClass.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					String catalogName = metaData.getColumnLabel(i); //获取列名
//					System.out.println(catalogName);
					String value = String.valueOf( resultSet.getObject(i)); //列值 转成字符串的形式
					BeanUtils.copyProperty(t,catalogName,value);  //属性拷贝
				}

			}

			return t;
		} catch (SQLException | InvocationTargetException | IllegalAccessException | InstantiationException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JDBCUtils.close(null,ps,resultSet);
		}

	}

	public static int count(String sql,Object...args){
		PreparedStatement patm = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			Connection conn = getConnection();

			patm = conn.prepareStatement(sql);

			for (int i = 1; i <= args.length; i++) {
				patm.setObject(i,args[i-1]);
			}

			resultSet = patm.executeQuery();

			if (resultSet.next()){
				count = resultSet.getInt(1);
			}

		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {

			JDBCUtils.close(null,patm,resultSet);
		}

		return count;



	}



}
